package com.example.springdome;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileWriter;
import java.math.BigDecimal;
import java.util.*;

/**
 * 表格统计 -修改版本（新增统计结果输出并且自动生成对应的记事本文件
 */
public class ExcelColumnCounter {
    public static void main(String[] args) {
        String excelFilePath = "C:\\Users\\28484\\Desktop\\水云组团_277.xlsx"; // 替换为你的文件路径
        int columnIndex = 9; // 指定要统计的列，第一列的索引为0
        String setFileNmae = "水云组团";//替换成需要输出的文件名称
        String outputFilePath = "C:\\Users\\28484\\Desktop\\" + setFileNmae + ".txt"; // 输出文件的路径

        Map<BigDecimal, Integer> intermediateMap = new HashMap<>(); // 存储BigDecimal和计数
        int totalCount = 0; // 用于统计总个数
        try (FileInputStream inputStream = new FileInputStream(excelFilePath);
             Workbook workbook = new XSSFWorkbook(inputStream);
             FileWriter writer = new FileWriter(outputFilePath)) { // 创建FileWriter对象
            Sheet sheet = workbook.getSheetAt(0); // 获取第一个工作簿
            // 跳过标题行，假设第一行是标题
            Row firstRow = sheet.getRow(0);
            if (firstRow == null) {
                throw new IllegalArgumentException("指定的工作表为空");
            }
            for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
                Row row = sheet.getRow(rowIndex);
                if (row != null) {
                    Cell cell = row.getCell(columnIndex);
                    if (cell != null) {
                        // 将单元格内容转换为字符串
                        DataFormatter formatter = new DataFormatter();
                        String cellValue = formatter.formatCellValue(cell).trim();

                        // 转换为BigDecimal
                        BigDecimal valueAsNumber = new BigDecimal(cellValue);

                        // 更新统计数据
                        intermediateMap.put(valueAsNumber, intermediateMap.getOrDefault(valueAsNumber, 0) + 1);
                        totalCount++; // 增加总个数
                    }
                }
            }

            // 将Map转换为List并排序
            List<Map.Entry<BigDecimal, Integer>> sortedEntries = new ArrayList<>(intermediateMap.entrySet());
            sortedEntries.sort(Map.Entry.comparingByKey());

            // 打印和写入结果
            System.out.println(setFileNmae + "投票结果");
            writer.write(setFileNmae + "投票结果\n");

            for (Map.Entry<BigDecimal, Integer> entry : sortedEntries) {
                String line = "金额：" + entry.getKey().toPlainString() + "元 => " + entry.getValue() + "票\n";
                writer.write(line);
                System.out.println(line.trim());
            }

            String totalLine = "投票总数: " + totalCount + "票\n";
            System.out.println(totalLine);
            writer.write(totalLine);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}